# encoding: utf-8
class OpenSourceProjectsController < ApplicationController

  before_filter :find_osp, :only => [:show, :master_apply, :accept_master_apply, :refuse_master_apply]
  before_filter :require_master, :only => [:master_apply, :accept_master_apply, :refuse_master_apply]

  helper :sort
  include SortHelper
  include OpenSourceProjectsHelper
  # GET /open_source_projects
  # GET /open_source_projects.json
  def index

    @app_dir = params[:app_dir]
    @language = params[:language]
    @created_time = params[:created_time]
    per_page_option = 10

    #@open_source_projects = OpenSourceProject.all

    @projects_count = OpenSourceProject.count
   # @projects_count = 10000
    unless (params[:topnum] == nil)
      @projects_count = params[:topnum].to_i
    end
    @projects_pages = Paginator.new @projects_count, per_page_option, params['page']
    @open_source_projects = OpenSourceProject.where("filtration != 0").order("composite_score DESC").offset(@projects_pages.offset).limit(@projects_pages.per_page)
    @bugs=nil
    respond_to do |format|
      format.html # index.html.erb
      format.json { render json: @open_source_projects }
    end
  end

  def master_apply
    @apply = @open_source_project.apply_tips
    @applicants = @open_source_project.applicants

    respond_to do |format|
      format.html {
        render :layout => "base_opensource_p"
      }
      format.json { render json: @open_source_project }
    end
  end

  # GET /open_source_projects/1
  # GET /open_source_projects/1.json
  def show
    upgrading = false
    if upgrading
      render :template => 'open_source_projects/upgrading'
    else
      per_page_option = 10

      #@open_source_project.update_column(:view_num_ossean, (@open_source_project.view_num_ossean.to_i+1))
      #@open_source_project.save
      # @pops=@open_source_project.open_source_project_popularities.where("year_col >= 2013").order("year_col ASC, month_col ASC")
      # @hot_words=@open_source_project.hot_words
      @memo = RelativeMemo.new(:open_source_project => @open_source_project)
      @topics_count = @open_source_project.relative_memos_num
      # @bugs = @open_source_project.relative_bugs.limit(6)
      
      ###########推荐#############################################################
      @simility_osp_array ,@simility_id_array,@simility_weight_array ,@simility_osp_des= recommend(params[:id],"simility")

      @relation_osp_array ,@relation_id_array,@relation_weight_array ,@relation_osp_des= recommend(params[:id],"relation")

      respond_to do |format|
        format.html {
          render :layout => "base_opensource_p"
        }
        format.json { render json: @open_source_project }
      end
    end
  end
  
  #按类型、排序方式获取项目关联的帖子
  def show_memos_by_type
    osp_id = params[:osp_id].to_i
    @memo_type = params[:memo_type].to_s
    @sort_by = params[:sort_by].to_s
    @open_source_project = OpenSourceProject.find(osp_id)
    find_relative_topics(@memo_type, @sort_by)
    respond_to do |format|
      format.html {
        render :layout => nil
      }
    end
  end
  
  #异步加载项目详情页的帖子tab页
  def show_memos_tab
    osp_id = params[:osp_id].to_i
    @open_source_project = OpenSourceProject.find(osp_id)
    find_osp_relative_topics
    #@topics_count = @open_source_project.relative_topics.count
    respond_to do |format|
      format.html {
        render :layout => nil
      }
    end
  end
  
  #异步加载项目详情页的态势分析tab页
   def show_analysis_tab
    osp_id = params[:osp_id].to_i
    @open_source_project = OpenSourceProject.find(osp_id)
    ###########start 用于页面展示当前项目对应的highcharts图表######################

    #chart1(当前项目月份与职位需求变化)
    @dataArray,@data2Array,@data3Array = chart1(osp_id)

    #chart2(当前项目地区与职位需求变化)
    @dataArray_2,@cityArray_2,@rangeArray_2 = chart2(osp_id)

    #chart5(当前项目工作经验需求对比分析 饼图)
    @dataArray_3,@experienceArray_3,@rangeArray_3 = chart5(osp_id)
 ###########end 用于页面展示当前项目对应的highcharts图表########################
 
    ###########推荐#############################################################
    @simility_osp_array ,@simility_id_array,@simility_weight_array ,@simility_osp_des= recommend(osp_id,"simility")
    #一个软件与推荐的几个热门软件职位需求和薪资情况对比 对应变量@simility_osp_array
    @chart7_osp, @chart7_count, @chart7_drilldown_city, @chart7_drilldown_count = chart7(@simility_osp_array,osp_id)
    @chart8_osp, @chart8_value, @chart8_drilldown_city, @chart8_drilldown_value = chart8(@simility_osp_array,osp_id)    
    respond_to do |format|
      format.html {
        render :layout => nil
      }
    end
  end
  
  #异步加载更多标签
  def _tag_name
    osp_id = params[:osp_id].to_i
    @object_flag = params[:object_flag].to_i
    @obj = OpenSourceProject.find(osp_id)
    @tags = @obj.tags
    @non_list_all = false
    respond_to do |format|
      format.html {
        render :layout => nil
      }
    end
  end



  #将搜索关键词中的程序语言相关词分离出来
  def getSearchWords(key_words)
    language_array = ["java","c++","ruby","php"," c ","c#","vb "," vb","delphi","asp "," asp"]
    #p = /[^\u4e00-\u9fa5]/ 
    #search_words1 = key_words.gsub(p, '')
    search_words1 = key_words.unpack("U*").select{ |p|  (0x4e00..0x9fa5).member? p }.pack("U*") 
    if search_words1 != ''
      search_words_str = key_words.gsub(search_words1, ' ')
    else
      search_words_str = key_words
    end
    search_words_str = ' ' + search_words_str + ' '
    search_words = ' ' + key_words + ' '
    #search_words_str.force_encoding("UTF-8")
    language_array.each do |language|
      if search_words_str.include? language
        search_words = search_words.gsub(language,' ')
        @language = language
      end
    end
    #search_words = search_words.unpack("U*");
    return search_words
  end

  def search
    require 'logger'
    logger = Logger.new(STDOUT)  #输出到控制台

    if params[:name].nil? || params[:name] == ""
      respond_to do |format|
        format.html { redirect_to open_source_projects_url }
        format.json { head :no_content }
      end 
    else
      #关键词预处理
      @name = params[:name].downcase #+ '~2'
      @app_dir = params[:app_dir]
      @language = params[:language].to_s.downcase
      @created_time = params[:created_time]
      key_words=''
      key_words << @name if @name
      key_words << ' ' + @language if @language
      search_words = getSearchWords(key_words)
      search_words = search_words.strip
      language = ''
      language << @language
      #search_words << ' ' + @language if @language


      #access java webservice by zyr
      oldverb = $VERBOSE; $VERBOSE = nil
      require 'iconv'
      $VERBOSE = oldverb

      require 'rubygems'
      require 'soap/wsdlDriver'

      a_URL = "http://localhost:8083/testWebService_war/services/solrquery?wsdl"
      soap_client = SOAP::WSDLDriverFactory.new(a_URL).create_rpc_driver

      #params = {:arg0 => "rails"}
      result = soap_client.query(@name)
      #logger.info(result)
      return_projects = []
      #project_ids = [770839,3674257,139,15397,98681,209133,770319,770675,770684,770839,907638,988140,1032900,1032911,1116837,1185243,1185345]

      result.each do |id|
        id = id.to_i
        project = OpenSourceProject.find_by_id(id)
        return_projects << project
      end

      require 'will_paginate/array'

      per_page_option = 10
      @projects_count = return_projects.length.to_i
      @projects_pages = Paginator.new @projects_count, per_page_option, params['page'] #custom paginator,to show the 10 records the default paginator of solr gets

      logger.info(@projects_pages.page)
      current_page = @projects_pages.page
      @open_source_projects = return_projects[(current_page-1)*per_page_option,per_page_option]
      respond_to do |format|
        format.html { render :layout => "base" }
      end

    end
  end


      # 请求solr单机服务
      #require 'net/http'

      # parse_string = "http://localhost:8080/solr/production1/select?q=" + @name
      # #parse_string.gsub! " ","%20"
      # url = URI.parse(parse_string)
      # req = Net::HTTP::Get.new(url.to_s)
      # res = Net::HTTP.start(url.host, url.port) {|http|
      #   http.request(req)
      # }
      # return_items = res.body
      # #logger.info(return_items)
      # require 'json'
      # return_items = JSON.parse(star_items)["items"]
      #_projects = []
      # 查询出id对应的项目



      #合成solr请求
=begin
      search = OpenSourceProject.search do
        without(:filtration,0)
        # with(:created_time, Date.new(params[:created_time].to_i, 01, 01)..Date.new(params[:created_time].to_i+1, 01, 01)) if params[:created_time].present? && !('earlier'.eql? params[:created_time])
        # with(:created_time, Date.new(1970, 01, 01)..Date.new(2010, 01, 01)) if params[:created_time].present? && ('earlier'.eql? params[:created_time])
        all do
          fulltext language do
            fields(:tags)
            fields(:language)
            #boost(function { log(:composite_score)^1000 })
          end
          fulltext search_words do
            #highlight(:name)
            fields(:name)
            fields(:tags_for_search)
            fields(:description)
            fields(:tags)
            fields(:language)
            boost_fields :name => 4.0
            boost_fields :tags_for_search => 3.0
            boost_fields :tags => 1.0
            boost_fields :description => 0.5
            boost_fields :language => 1.0
            #boost(function { log(sum(:composite_score, 1))^2000 })
            boost(function { log(:composite_score)^1000 })
          end
        end
=end
        #adjust_solr_params do |params|
        #  params[:q] += " AND something_s:more"
        #end
        #order_by_function(:sum, :view_num_ossean, [:product, :composite_score, '0.01'], :desc)
      #   if "".eql?(search_words)
      #     order_by(:composite_score, :desc)
      #   else
      #     order_by(:score, :desc)
      #   end
      #   paginate :page => params[:page], :per_page => 10  # the default paginator of solr,10 records for each page
      # end
      # per_page_option = 10
      # @hits = search.hits
      # @open_source_projects = search.results
      # @projects_count = search.total #get total count of search



  def showbug
    @open_source_project = OpenSourceProject.find(params[:id])
    @project_info=@open_source_project.open_source_project_infos.first
    sort_init 'updated_time', 'desc'
    sort_update 'created_time' => "#{RelativeMemo.table_name}.created_time",
                'replies' => "#{RelativeMemo.table_name}.replies_count"
    #'updated_time' => "COALESCE (last_replies_relative_memos.created_time, #{RelativeMemo.table_name}.created_time)"

    @memo = RelativeMemo.new(:open_source_project => @open_source_project)
    @topic_count = @open_source_project.bugs.count
    @topic_pages = Paginator.new @topic_count, per_page_option, params['page']
    @memos = @open_source_project.bugs.
        reorder("#{RelativeMemo.table_name}.sticky DESC").
        includes(:last_reply).
        limit(@topic_pages.per_page).
        offset(@topic_pages.offset).
        order(sort_clause).
        all

    respond_to do |format|
      format.html {
        render :layout => "base_opensource_p"
      }
      format.json { render json: @open_source_project }
    end
  end

  def showmemo
    @open_source_project = OpenSourceProject.find(params[:id])

    sort_init 'updated_time', 'desc'
    sort_update 'created_time' => "#{RelativeMemo.table_name}.created_time",
                'replies' => "#{RelativeMemo.table_name}.replies_count"
    #'updated_time' => "COALESCE (last_replies_relative_memos.created_time, #{RelativeMemo.table_name}.created_time)"
    @project_info=@open_source_project.open_source_project_infos.first

    @memo = RelativeMemo.new(:open_source_project => @open_source_project)
    @topic_count = @open_source_project.topics.count
    @topic_pages = Paginator.new @topic_count, per_page_option, params['page']
    @memos = @open_source_project.topics.
        reorder("#{RelativeMemo.table_name}.sticky DESC").
        includes(:last_reply).
        limit(@topic_pages.per_page).
        offset(@topic_pages.offset).
        order(sort_clause).
        all

    respond_to do |format|
      format.html {
        render :layout => "base_opensource_p"
      }
      format.json { render json: @open_source_project }
    end
  end

  # GET /open_source_projects/new
  # GET /open_source_projects/new.json
  def new
    @open_source_project = OpenSourceProject.new

    respond_to do |format|
      format.html # new.html.erb
      format.json { render json: @open_source_project }
    end
  end

  # GET /open_source_projects/1/edit
  def edit
    @open_source_project = OpenSourceProject.find(params[:id])
  end

  # POST /open_source_projects
  # POST /open_source_projects.json
  def create
    @open_source_project = OpenSourceProject.new(params[:open_source_project])

    respond_to do |format|
      if @open_source_project.save
        format.html { redirect_to @open_source_project, notice: 'Open source project was successfully created.' }
        format.json { render json: @open_source_project, status: :created, location: @open_source_project }
      else
        format.html { render action: "new" }
        format.json { render json: @open_source_project.errors, status: :unprocessable_entity }
      end
    end
  end

  # PUT /open_source_projects/1
  # PUT /open_source_projects/1.json
  def update
    @open_source_project = OpenSourceProject.find(params[:id])

    respond_to do |format|
      if @open_source_project.update_attributes(params[:open_source_project])
        format.html { redirect_to @open_source_project, notice: 'Open source project was successfully updated.' }
        format.json { head :no_content }
      else
        format.html { render action: "edit" }
        format.json { render json: @open_source_project.errors, status: :unprocessable_entity }
      end
    end
  end

  # DELETE /open_source_projects/1
  # DELETE /open_source_projects/1.json
  def destroy
    @open_source_project = OpenSourceProject.find(params[:id])
    @open_source_project.destroy

    respond_to do |format|
      format.html { redirect_to open_source_projects_url }
      format.json { head :no_content }
    end
  end

  def remove_condition
    @app_dir = params[:app_dir]
    @language = params[:language]
    @created_time = params[:created_time]
    redirect_to search_open_source_projects_path(:app_dir => @app_dir, :language => @language, :created_time => @created_time, :name => params[:name])
  end


  #gcm
  def welcome

    @app_dir = params[:app_dir]
    @language = params[:language]
    @created_time = params[:created_time]

    @projects_count = OpenSourceProject.count
    #@posts_count = RelativeMemo.count
    @posts_count = 14243800
    #@open_source_projects=OpenSourceProject.find_hot_osps(20)
    @projects_for_cloud = OpenSourceProject.where("filtration != 0").order("composite_score desc").limit(60)
    
    # begin: 读取排行榜的结果 张迅晖 2017-10-14
    @ossean_rank_result_map = Hash.new
    distinct_types = OsseanRank.select(:rank_type).uniq
    distinct_types.each do |item|
      rank_type = item.rank_type
      tmp = OsseanRank.where(["rank_type = ?", rank_type]).order("rank asc")
      @ossean_rank_result_map[rank_type] = tmp
    end
    # end: 读取排行榜的结果 张迅晖 2017-10

    render :layout => "base_welcome"
    #respond_to do |format|
    #  format.html{render template: "open_source_projects/welcome"}
    #end
  end

  #gcm end

  def refuse_master_apply
    @apply = ApplyProjectMaster.where("user_id = ? and apply_id = ? and apply_type = 'OpenSourceProject'", params[:user_id], @open_source_project.id)
    @apply.first.destory

    redirect_to master_apply_open_source_project_path
  end

  def accept_master_apply
    @apply = ApplyProjectMaster.where("user_id = ? and apply_id = ? and apply_type = 'OpenSourceProject'", params[:user_id], @open_source_project.id)
    if @apply.count == 1
      @apply.first.update_attributes(:status => 2)
    end

    redirect_to master_apply_open_source_project_path
  end



  #echarts表的ajax函数
  def ajax
    name = params[:name]
    osp = OpenSourceProject.find(:first, :select => 'id', :conditions => ['name=?',name])
    id = osp.id

    respond_to do |format|
      format.json {render :json => {:osp_id => id}}
    end
  end

  private

  def require_master
    render_403 unless @open_source_project.admin?(User.current)
  end

  def find_osp
    @open_source_project = OpenSourceProject.find(params[:id])
      #@project_info=@open_source_project.open_source_project_infos.first
  rescue ActiveRecord::RecordNotFound
    render_404 unless @open_source_project.present?
  end

   #分页查找项目关联的不同类型帖子
  def find_relative_topics(memo_type, sort_by)
    #approach 1
    per_page_option = 10
    RelativeMemoToOpenSourceProject.set_table_name RelativeMemoToOpenSourceProject.getTableName @open_source_project.id

    @news_count = RelativeMemoToOpenSourceProject.includes(:relative_memos).where(osp_id: @open_source_project.id, memo_type: 'news').count
    @blogs_count = RelativeMemoToOpenSourceProject.includes(:relative_memos).where(osp_id: @open_source_project.id, memo_type: 'blog').count
    @posts_count = RelativeMemoToOpenSourceProject.includes(:relative_memos).where(osp_id: @open_source_project.id, memo_type: 'topic').count
    @topics_count = (memo_type.eql? "news") ? @news_count : ((memo_type.eql? "blog") ? @blogs_count : @posts_count)

    #@topics_count = RelativeMemoToOpenSourceProject.includes(:relative_memos).where(osp_id: @open_source_project.id, memo_type: memo_type).count
    @topics_pages = Paginator.new @topics_count, per_page_option, params['page']
    @relative_memo_ids=RelativeMemoToOpenSourceProject.where(osp_id: @open_source_project.id, memo_type: memo_type).order(sort_by).offset(@topics_pages.offset).limit(@topics_pages.per_page)
  end

  #get memos' info by memo_type
  def get_memos_info_by_type memo_type, osp_id, rmtosp_name, size
    memos = RelativeMemoToOpenSourceProject.find_by_sql ["SELECT r.* FROM #{rmtosp_name} mt" +
                                                         " INNER JOIN relative_memos r ON mt.relative_memo_id = r.id" +
                                                         " WHERE mt.osp_id = #{osp_id} AND r.memo_type = 'news' ORDER BY r.created_time DESC LIMIT #{size} "]
  end

  #get memos' count by memo_type
  def get_memos_count_by_type memo_type, osp_id, rmtosp_name, size
    memos_count = (RelativeMemoToOpenSourceProject.find_by_sql ["SELECT r.id FROM #{rmtosp_name} mt" +
                                                         " INNER JOIN relative_memos r ON mt.relative_memo_id = r.id" +
                                                         " WHERE mt.osp_id = #{osp_id} AND r.memo_type = #{memo_type}"]).count
    return memos_count
  end
  # find relative_topics of osp order by
  def find_osp_relative_topics

    size = 5

    #rmtosp_name = RelativeMemoToOpenSourceProject.getTableName @open_source_project.id
    RelativeMemoToOpenSourceProject.set_table_name RelativeMemoToOpenSourceProject.getTableName @open_source_project.id
    #@relative_memo_ids=RelativeMemoToOpenSourceProject.includes(:relative_memos).where(osp_id: @open_source_project.id).order('match_weight DESC').limit(10)#.pluck(:relative_memo_id)

    #@news = get_memos_info_by_type "'news'", @open_source_project.id, rmtosp_name, size
    #@news_count = get_memos_count_by_type "'news'", @open_source_project.id, rmtosp_name, size
    # @news = RelativeMemoToOpenSourceProject.find_by_sql ["SELECT r.* FROM #{rmtosp_name} mt" +
                                                         # " INNER JOIN relative_memos r ON mt.relative_memo_id = r.id" +
                                                         # " WHERE mt.osp_id = #{@open_source_project.id} AND r.type = 'news' ORDER BY r.created_time DESC LIMIT #{size} "]
    # @news_count = (RelativeMemoToOpenSourceProject.find_by_sql ["SELECT r.id FROM #{rmtosp_name} mt" +
                                                         # " INNER JOIN relative_memos r ON mt.relative_memo_id = r.id" +
                                                         # " WHERE mt.osp_id = #{@open_source_project.id} AND r.type = 'news'"]).count
    @news = RelativeMemoToOpenSourceProject.includes(:relative_memos).where(osp_id: @open_source_project.id, memo_type: 'news').order('created_time DESC').limit(size)
    @news_count = RelativeMemoToOpenSourceProject.includes(:relative_memos).where(osp_id: @open_source_project.id, memo_type: 'news').count

    #@blogs = get_memos_info_by_type "'blog'", @open_source_project.id, rmtosp_name, size
    #@blogs_count = get_memos_count_by_type "'blog'", @open_source_project.id, rmtosp_name, size

    #@posts = get_memos_info_by_type "'topic'", @open_source_project.id, rmtosp_name, size
    #@posts_count = get_memos_count_by_type "'topic'", @open_source_project.id, rmtosp_name, size
    @blogs = RelativeMemoToOpenSourceProject.includes(:relative_memos).where(osp_id: @open_source_project.id, memo_type: 'blog').order('created_time DESC').limit(size)
    @blogs_count = RelativeMemoToOpenSourceProject.includes(:relative_memos).where(osp_id: @open_source_project.id, memo_type: 'blog').count
#
    @posts = RelativeMemoToOpenSourceProject.includes(:relative_memos).where(osp_id: @open_source_project.id, memo_type: 'topic').order('created_time DESC').limit(size)
    @posts_count = RelativeMemoToOpenSourceProject.includes(:relative_memos).where(osp_id: @open_source_project.id, memo_type: 'topic').count

        #@relative_topics=RelativeMemo.where(id: @relative_memo_ids).group('title').order('review_num DESC').limit(10)
    #@tech_trend_topics_ids=RelativeMemoToOpenSourceProject.where(osp_id: @open_source_project.id, memo_type: 'news').order('created_time DESC, match_weight DESC, review_num DESC').limit(7) #.pluck(:relative_memo_id)
    #@tech_trend_topics=RelativeMemo.where(id: @tech_trend_topics_ids).order('review_num DESC')
  end

  #用于在每个项目详情页面上显示对应职位工资信息表而定义的函数（查询数据库中的数据）
  def getHighcharts

  end



  #相似度推荐算法的数据提取
  def recommend(id , flag)
    size = 5
    ospArray = []
    weightArray = []
    idArray = []
    desArray=[]
    tmp_result = []

    # 用于存储相似项目的id
    relativeIdList = []
    unrelativeIdList = []
    SimilityRecommend.set_table_name SimilityRecommend.getTableName id
    RelationRecommend.set_table_name RelationRecommend.getTableName id
    if flag == "simility"
      tmp_result = SimilityRecommend.select("relative_osp_id , weight").where("osp_id=? and weight >=0",id).order("weight desc").limit(size)

    elsif flag == "relation"
      tmp_result = RelationRecommend.select("relative_osp_id , weight").where("osp_id=? and weight >=0",id).order("weight desc").limit(size)
    end

 #   tmp_result = SimilityRecommend.select("relative_osp_id , weight").where("osp_id=? and weight != ?",id,1.0).limit(10)

    if tmp_result.length == 0
       return nil
    end

    tmp_result.each do|line|
      relative_osp_id = line.relative_osp_id
      weight = line.weight.to_s;
      relative_osp = OpenSourceProject.find(relative_osp_id)
      ospDes = relative_osp.description
      #logger.info("***********************")
      #logger.info(ospDes)
      if ospDes.nil? || ospDes.empty?
        desArray<<"..."
      else
        desArray<<ospDes[0, 60]+" ..."
      end
      #desArray<<relative_osp.description.truncate(100, :seperator=>'|:|')#truncate(relative_osp.description, 100)
      ospArray<<relative_osp
      weightArray<<weight
      idArray<<id
    end

    return ospArray , idArray , weightArray,desArray
  end


  #定义第一个图的函数（根据项目获取每个月份的职位需求和薪资情况） 参数为osp对应的id
  def chart1(id)
    #定义返回值
    dataArray = []
    data2Array = []
    data3Array = []
    countArray = []
    yearArray = []
    salary_min = []
    salary_max = []
    #根据osp_id查所有对应的relative_memo_id
    highcharts_relative_memo_ids = Highchart.find(:all, :select => 'distinct relative_memo_id', :conditions => ['osp_id=?',id])
    #根据所有relative_memo_id查询对应的发布时间
    str_in = changeResultsToStringSplitByComma(highcharts_relative_memo_ids)
    if str_in == ''
      return nil,nil,nil
    else
      #查找最早和最晚的年份
      jobRequirement_maxYear_minYear = JobRequirement.find_by_sql("select max(year(created_time)) as max_year, min(year(created_time)) as min_year from job_requirements where id in (" + str_in + ") and created_time is not null")
    end
    max_year = jobRequirement_maxYear_minYear[0].max_year.to_i
    min_year = jobRequirement_maxYear_minYear[0].min_year.to_i
    if max_year == min_year
      return nil,nil,nil
    end
    for i in (min_year..max_year)
      yearArray << i.to_i
      #查询第i年1~12月份的所有职位需求 同时顺便查询薪资
      for j in (1..12)
        #职位需求
        jobRequirement_count = JobRequirement.find_by_sql("select count(*) as num from job_requirements where id in (" + str_in + ") and year(created_time)=" + i.to_s + " and month(created_time)=" + j.to_s)
        count = jobRequirement_count[0].num.to_i
        countArray << count

        #薪资情况
        highcharts_avg_min_max_salary = Highchart.find(:first, :select => 'avg(min_salary) as avg_min_salary, avg(max_salary) as avg_max_salary', :conditions => ['year(jr.created_time)=? and month(jr.created_time)=? and hi.osp_id=?',i.to_s,j.to_s,id.to_s],
                                                    :joins => 'as hi inner join job_requirements as jr on hi.relative_memo_id=jr.id')
        avg_min_salary = highcharts_avg_min_max_salary.avg_min_salary
        avg_max_salary = highcharts_avg_min_max_salary.avg_max_salary
        if avg_min_salary.nil?
          avg_min_salary = 0
        end
        if avg_max_salary.nil?
          avg_max_salary = 0
        end
        salary_min << avg_min_salary.to_i
        salary_max << avg_max_salary.to_i
      end
    end
    #组合dataArray
    i = 1
    while i <= yearArray.length
      year = yearArray[i-1]
      for month in (1..12)
        #构造Time
        if month / 10 == 0
          time = year.to_s + "0" + month.to_s + "01"
        else
          time = year.to_s + month.to_s + "01"
        end
        time = Time.parse(Date.parse(time).strftime('%Y-%m-%d')).to_i * 1000
        count = countArray[(i-1)*12 + month - 1]
        min = salary_min[(i-1)*12 + month - 1]
        max = salary_max[(i-1)*12 + month - 1]
        data = [time,count]
        data2 = [time,min]
        data3 = [time,max]
        dataArray << data
        data2Array << data2
        data3Array << data3
      end
      i = i + 1
    end
    return dataArray,data2Array,data3Array
  end

  #定义第二个图的函数（根据项目获取每个地区职位需求和薪资情况）参数为osp对应的id
  def chart2(id)
    dataArray = []
    cityArray = []
    countArray_city = []
    countSum = 0

    rangeArray = []#第二个图表的返回值
    cityResultArray = []
    highcharts_city_count = Highchart.find(:all, :select => 'city,count(*) as num', :conditions => ['osp_id=? and city is not null',id],
                                          :group => 'city', :order => 'num desc')
    if highcharts_city_count.length == 0
      return nil
    end
    highcharts_city_count.each do |h|
      city = h.city.to_s
      count = h.num.to_i
      countSum = countSum + count
      cityArray << city
      countArray_city << count
    end

    #计算所有百分比大于等于5%的
    sumRate = 0.0
    for i in (0..cityArray.length - 1)
      city = cityArray[i].to_str
      count = countArray_city[i]
      rate = count * 1.0 / countSum * 100
      if rate >= 5
        sumRate = sumRate + rate
        data = [city,rate]
        dataArray << data

        #第二张图表
        cityResultArray << city
        highchart_avg_min_max_salary = Highchart.find(:first,:select => 'avg(min_salary) as avg_min_salary, avg(max_salary) as avg_max_salary', :conditions => ['city=? and osp_id=?',city,id])
        avg_min_salary = highchart_avg_min_max_salary.avg_min_salary.to_i
        avg_max_salary = highchart_avg_min_max_salary.avg_max_salary.to_i
        range = [avg_min_salary,avg_max_salary]
        rangeArray << range
      end
    end
    if dataArray.length < cityArray.length
      otherRate = 100 - sumRate
      data = ['其他',otherRate]
      dataArray << data

      #第二张图表
      str_in = changeStrArrayToStringSplitByComma(cityResultArray)
      if str_in == ""
        str_in = "\'-1\'"#随便赋值 不能是存在的city
      end
      highchart_avg_min_max_salary_others = Highchart.find_by_sql("select avg(min_salary) as avg_min_salary, avg(max_salary) as avg_max_salary from highcharts where osp_id=" + id.to_s + " and city not in (" + str_in + ") and city is not null")
      avg_min_salary = highchart_avg_min_max_salary_others[0].avg_min_salary.to_i
      avg_max_salary = highchart_avg_min_max_salary_others[0].avg_max_salary.to_i
      range = [avg_min_salary,avg_max_salary]
      rangeArray << range
      cityResultArray << '其他'
    end
    return dataArray,cityResultArray,rangeArray
  end

  #定义当前项目工作经验需求对比分析 饼图
  def chart5(id)
    dataArray = []
    experienceArray = []
    countArray = []
    rangeArray = []#记录平均最小、最大工资范围
    #查找experience字段
    highcharts_experience_count = Highchart.find(:all, :select => 'experience,count(*) as num', :conditions => ['osp_id=? and experience!=?',id,''],
                                         :group => 'experience', :order => 'num desc')
    if highcharts_experience_count.length == 0
      dataArray = nil
    else
      countSum = 0
      highcharts_experience_count.each do |h|
        experience = h.experience.to_s
        count = h.num.to_i
        countSum = countSum + count
        experienceArray << experience
        countArray << count
      end
      for i in (0..experienceArray.length - 1)
        experience = experienceArray[i]
        count = countArray[i]
        rate = count * 1.0 / countSum * 100
        data = [experience, rate]
        dataArray << data
      end

      #查找当前项目和experience对应的avg_salary_min/max
      experienceArray.each do |e|

        highchart_avg_min_max_salary = Highchart.find(:first, :select => 'avg(min_salary) as avg_min_salary, avg(max_salary) as avg_max_salary', :conditions => ['osp_id=? and experience=?',id,e])
        avg_min_salary = highchart_avg_min_max_salary.avg_min_salary.to_i
        avg_max_salary = highchart_avg_min_max_salary.avg_max_salary.to_i
        range = [avg_min_salary,avg_max_salary]
        rangeArray << range
      end
    end
    return dataArray,experienceArray,rangeArray
  end

  #绘制当前开源软件与相似开源软件职位需求数量对比柱状图
  def chart7(simility_osp_array,id)

    jobRequirements_osp = [] #记录用于显示的6个开源软件对象
    jobRequirements_count = []
    jobRequirements_drilldown_city = []
    jobRequirements_drilldown_count = []

    #查找当前id对应的项目
    osp_current = OpenSourceProject.find(id)
    #找相似软件中最热的5个软件
    hotOsps = hottestN_osp(simility_osp_array,5)
    #将当前软件插入到热门项目的第一个下标位置
    hotOsps.insert(0,osp_current)
    hotOsps.each do |osp|
      #对每个项目查找职位需求数量
      highchart_countItems = Highchart.find(:first, :select => "count(*) as num", :conditions => ['osp_id=?',osp.id])
      countItems_simility = highchart_countItems.num.to_i #项目职位需求数量
      jobRequirements_count << countItems_simility #将相似软件的结果存储到结果数组中
      jobRequirements_osp << osp
    end

    #查找每个软件对应6个城市的职位需求情况-drilldown
    hotOsps.each do |osp|
      #首先查找当前软件的热门城市和对应的职位需求数量
      jobRequirements_drilldown_city_tmp, jobRequirements_drilldown_count_tmp = hottestN_cityAndCount_by_osp(osp,6)
      jobRequirements_drilldown_city << jobRequirements_drilldown_city_tmp
      jobRequirements_drilldown_count << jobRequirements_drilldown_count_tmp
    end

    return jobRequirements_osp,jobRequirements_count,jobRequirements_drilldown_city,jobRequirements_drilldown_count

  end

   #绘制当前开源软件与相似开源软件薪资对比柱状图
  def chart8(simility_osp_array,id)

    salary_osp = [] #记录用于显示的6个开源软件对象
    salary_value = []
    salary_drilldown_city = []
    salary_drilldown_value = []

    #查找当前id对应的项目
    osp_current = OpenSourceProject.find(id)
    #找相似软件中最热的5个软件
    hotOsps = hottestN_osp(simility_osp_array,5)
    #将当前软件插入到热门项目的第一个下标位置
    hotOsps.insert(0,osp_current)
    hotOsps.each do |osp|
      #对每个项目查找最高和最低薪资的均值
      highchart_salary = Highchart.find(:first, :select => "avg(min_salary) as min, avg(max_salary) as max", :conditions => ['osp_id=?',osp.id])
      avg_min_salary = highchart_salary.min.to_f #当前项目最低工资的均值
      avg_max_salary = highchart_salary.max.to_f #当前项目最高工资的均值
      avg_salary = (avg_min_salary + avg_max_salary) / 2 #记录最高均值和最低均值的平均值作为最后的结果
      salary_value << avg_salary #将相似软件的结果存储到结果数组中
      salary_osp << osp
    end

    #查找每个软件对应6个城市的职位需求情况-drilldown
    hotOsps.each do |osp|
      #首先查找当前软件的热门城市和对应的薪资情况
      salary_drilldown_city_tmp, salary_drilldown_value_tmp = hottestN_cityAndSalary_by_osp(osp,6)
      salary_drilldown_city << salary_drilldown_city_tmp
      salary_drilldown_value << salary_drilldown_value_tmp
    end

    return salary_osp,salary_value,salary_drilldown_city,salary_drilldown_value

  end


  #将查询结果拼成（1,2,3）的形式
  def changeResultsToStringSplitByComma(array)
    result = ""
    if array.nil?
      return result
    end
    array.each do |a|
      result = result + a.relative_memo_id.to_s + ","
    end
    if result.length != 0
      result = result[0,result.length - 1]
    end
    return result
  end

  #将字符串数组变成1，2，3的形式
  def changeStrArrayToStringSplitByComma(array)
    result = ''
    if array.nil?
      return result
    end
    array.each do |a|
      result = result + "\'" + a.to_s + "\',"
    end
    if result.length != 0
      result = result[0,result.length - 1]
    end
    return result
  end

  #对datetime类型日期分离 年、月、日
  def splitDate(time)
    timeArray = time.to_s.split(' ')
    date = timeArray[0].split('-')
    return date[0],date[1],date[2]
  end

  #按年月时间将统计数据合并
  def mergeByYearAndMonth(yearArray, monthArray, countArray)
    i = 0
    while i < yearArray.length
      year = yearArray[i].to_s
      month = monthArray[i].to_s
      if i != 0
        if yearArray[i-1].to_i == yearArray[i].to_i && monthArray[i-1].to_i == monthArray[i].to_i
          yearArray.delete_at(i)
          monthArray.delete_at(i)
          countArray[i-1] = countArray[i-1] + countArray[i]
          countArray.delete_at(i)
          i = i - 1
        end
      end
      i = i + 1
    end

    return yearArray,monthArray,countArray
  end

  #计算数组中所有值的和
  def sum(array)
    result = 0
    array.each do |a|
      result = result + a.to_i
    end
    return result
  end

  #找到软件数组中对应的n个热门软件
  def hottestN_osp(array, n)
    result = [] #用来存储最热门的n个软件
    unless array.nil?
      array.each do |osp|
      score = osp.composite_score.to_f
      if result.length > 0
        for i in 0..result.length-1
          score_2 = result[i].composite_score.to_f #结果数组中已经存在的项目的score
          if score > score_2 #表示当前项目比之前添加到数组中的这个项目的score值大 则将这个项目添加到result中该项目前面 默认排阻是从大到小
            result.insert(i,osp)
            break
          end
          if i == result.length-1
            #表示比较结束 当前的项目热度比所有result数组中的项目都小 直接插到数组最后
            result << osp
          end
        end
      else
        #表示结果数组中还没有数据
        result << osp
      end #if result.length的结尾
    end #array循环的结尾
    end
    #对result结果数组进行截取
    if result.length > n
      result = result[0,n]
    end
    return result
  end

  #查找开源软件对应的热门的N个城市 和对应的职位需求数量
  def hottestN_cityAndCount_by_osp(osp,n)
    city = [] #记录排名靠前的n个
    count = [] #记录排名靠前的n个城市对应的职位需求数量
    highcharts_city_count = Highchart.find(:all, :select => "city,count(*) as num", :group => 'city', :conditions => ['osp_id=?',osp.id])
    highcharts_city_count.each do |item|
      city_tmp = item.city
      count_tmp = item.num.to_i
      #比较count值的大小
      if count.length == 0
        count << count_tmp
        city << city_tmp
      else
        for i in 0..count.length-1
          if count[i] < count_tmp #表示当前加入的数据值大于该下标对应的值 插入到该下标之前
            count.insert(i,count_tmp)
            city.insert(i,city_tmp)
            break
          end
          if i == count.length-1 #表示比较到最后一个数据还没有满足上面if的条件 则插入到结果数组的最后
            count << count_tmp
            city << city_tmp
          end
        end #for循环遍历count数组
      end #if length=0结尾
    end #highcharts_city_count遍历结尾
    if count.length > n
      city = city[0,n]
      count = count[0,n]
    end
    return city,count
  end # hottestN_cityAndCount_by_osp函数结尾


  #查找开源软件对应的热门的N个城市 和对应的平均薪资
  def hottestN_cityAndSalary_by_osp(osp,n)
    city = [] #记录排名靠前的n个
    count = [] #记录排名靠前的n个城市对应的职位需求数量
    avg_salary = [] #记录排名靠前的n个城市对应的薪资情况
    highcharts_city_count = Highchart.find(:all, :select => "city,count(*) as num", :group => 'city', :conditions => ['osp_id=?',osp.id])
    highcharts_city_count.each do |item|
      city_tmp = item.city
      count_tmp = item.num.to_i
      #比较count值的大小
      if count.length == 0
        count << count_tmp
        city << city_tmp
      else
        for i in 0..count.length-1
          if count[i] < count_tmp #表示当前加入的数据值大于该下标对应的值 插入到该下标之前
            count.insert(i,count_tmp)
            city.insert(i,city_tmp)
            break
          end
          if i == count.length-1 #表示比较到最后一个数据还没有满足上面if的条件 则插入到结果数组的最后
            count << count_tmp
            city << city_tmp
          end
        end #for循环遍历count数组
      end #if length=0结尾
    end #highcharts_city_count遍历结尾
    if count.length > n
      city = city[0,n]
      count = count[0,n]
    end

    #根据city数组中的数据查找对应的avg_salary
    city.each do |c|
      highchart_avg_salary = Highchart.find(:first, :select => "avg(max_salary) as max, avg(min_salary) as min", :conditions => ['osp_id=? and city=?',osp.id,c])
      avg_max_salary = highchart_avg_salary.max.to_f
      avg_min_salary = highchart_avg_salary.min.to_f
      avg_salary_tmp = (avg_max_salary+avg_min_salary)/2
      avg_salary << avg_salary_tmp
    end

    return city,avg_salary
  end # hottestN_cityAndSalary_by_osp函数结尾

end
